﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CNotebook.Inventory
{
    public class ProductSizeManager:ConnectionManager
    {
        public void AddSize(string _BrandID, string _CategoryCode, string _ProductCode, string _VariantCode, string _Size)
        {

            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "INSERT INTO M_BRND_CT_PRD_VRNT_SIZE (BrandID, CatgryCode, ProdCode, VrntCode, Size) VALUES (@BrandID, @CatgryCode, @ProdCode, @VrntCode, @Size)";
            oCommand.Parameters.Add(new SqlParameter("@BrandID", _BrandID));
            oCommand.Parameters.Add(new SqlParameter("@CatgryCode", _CategoryCode));
            oCommand.Parameters.Add(new SqlParameter("@ProdCode", _ProductCode));
            oCommand.Parameters.Add(new SqlParameter("@VrntCode", _VariantCode));
            oCommand.Parameters.Add(new SqlParameter("@Size", _Size));

            oCommand.ExecuteNonQuery();
        }

        public void UpdateSize(DataTable table)
        {
            SqlCommand oCommand = new SqlCommand();
            foreach (DataRow oRow in table.Rows)
            {
                if (oRow.RowState != DataRowState.Deleted)
                {
                    oCommand = new SqlCommand();
                    oCommand.Connection = this.Connection;
                    oCommand.CommandText = "DELETE M_BRND_CT_PRD_VRNT_SIZE WHERE BrandID=@BrandID AND CatgryCode=@CatgryCode and ProdCode=@ProdCode AND VrntCode=@VrntCode";
                    oCommand.Parameters.Add(new SqlParameter("@BrandID", oRow["BrandID"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@CatgryCode", oRow["CatgryCode"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@ProdCode", oRow["ProdCode"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@VrntCode", oRow["VrntCode"].ToString()));
                    
                    oCommand.ExecuteNonQuery();
                    break;
                }
            }

            foreach (DataRow oRow in table.Rows)
            {
                if (oRow.RowState != DataRowState.Deleted)
                {
                    oCommand = new SqlCommand();

                    oCommand.Connection = this.Connection;
                    oCommand.CommandText = "INSERT INTO M_BRND_CT_PRD_VRNT_SIZE (BrandID, CatgryCode, ProdCode, VrntCode, Size, UntMsr) VALUES (@BrandID, @CatgryCode, @ProdCode, @VrntCode, @Size, @UntMsr)";
                    oCommand.Parameters.Add(new SqlParameter("@BrandID", oRow["BrandID"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@CatgryCode", oRow["CatgryCode"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@ProdCode", oRow["ProdCode"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@VrntCode", oRow["VrntCode"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@Size", oRow["Size"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@UntMsr", oRow["UntMsr"].ToString()));

                    oCommand.ExecuteNonQuery();
                }
            }
        }

        public DataTable GetSizes(string _BrandID, string _CategoryCode, string _ProductCode, string _VariantCode)
        {
            DataSet ds = new DataSet();

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT *, cast(Size as nvarchar(10)) + UntMsr as SizeWithMsr FROM M_BRND_CT_PRD_VRNT_SIZE WHERE BrandID=@BrandID AND CatgryCode=@CatgryCode AND ProdCode=@ProdCode AND VrntCode=@VrntCode order by Size";
            oCommand.Parameters.Add(new SqlParameter("@BrandID", _BrandID));
            oCommand.Parameters.Add(new SqlParameter("@CatgryCode", _CategoryCode));
            oCommand.Parameters.Add(new SqlParameter("@ProdCode", _ProductCode));
            oCommand.Parameters.Add(new SqlParameter("@VrntCode", _VariantCode));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }
    }
}
